import json
from datetime import datetime

from product_upload.domain.amazon_us.amazon_us_record import AmazonUsRecord
from product_upload.domain.basic.basic_product import BasicProduct
from product_upload.util.basic.common_util import load_json
from product_upload.util.basic.mysql_util import db_list_by_page, db_batch_update, db_get_one, db_batch_insert

search_words_sys_prompt = """
Keyword Generation Instruction
Please generate a comprehensive set of Amazon product keywords based on the provided product information. Strictly adhere to the following requirements:
Generation Requirements:
1.Product Category or Subcategory: Clearly identify the product's category or subcategory (e.g., electronics, home goods, fashion accessories) to help generate more relevant keywords.
2.Primary Keywords: Keywords that directly describe the core functions or uses of the product.
3.Long-tail Keywords: More specific and targeted keywords with relatively lower competition, accurately capturing potential customers' search intent.
4.Related Keywords: Keywords related to the product but do not directly describe the product, including related accessories, usage scenarios, or target user groups.
5.Number of Keywords: Provide at least 15 keywords for each category to cover a broader search range.
6.Synonyms and Variations: Use synonyms, spelling variations, and different word orders when generating keywords to cover more potential search terms. For example, "wireless Bluetooth headphones" can vary to "Bluetooth wireless headphones."
7.Usage Scenarios and Target Users: Consider the product's usage scenarios (e.g., outdoor, office, travel) and target user groups (e.g., athletes, students, professionals) when generating related keywords.
8.JSON Format Requirements: Return the keywords in JSON format, grouped by category with clear labels. Separate keywords within each category with commas, ensuring correct syntax without trailing commas.
9.Adhere to Amazon’s Keyword Policies: Ensure that the keywords do not violate Amazon’s relevant policies by avoiding misleading information or unrelated popular terms.
10.Avoid the Following Content:
•Brand Names and Trademarks: Do not include any brand names or trademarks.
•Special Characters and Emojis: Do not use special characters such as ™, ®, €, ©, ±, ~, etc., and avoid any form of emojis.
•Prohibited Phrases:
•Do not use terms like "eco-friendly," "antimicrobial," "bamboo-based," "contains bamboo," "soy-made," or "contains soy," including any variants or synonyms related to these concepts.
•Do not include warranty and return policy wording such as "full refund," "unconditional guarantee," "satisfaction guaranteed," "money-back guarantee," etc.
•Do not use time-sensitive promotional or event information phrases like "holiday sale," "limited-time discount," "seasonal offer," etc.
•Inappropriate Content: Avoid any keywords related to pornography, violence, hate speech, or other inappropriate content.
•Promotional Phrasing: Do not use terms like "limited-time offer," "special price," "discount," "promotion," "sale," "price drop," etc.
•Shipping Details: Do not include phrases like "ships from [location] warehouse," "free shipping," "fast delivery," "next-day delivery," etc.
Self-Check Process:
Before finalizing and outputting the keywords, perform the following checks to ensure compliance and accuracy:
1.Verify Category Alignment:
•Ensure all keywords are relevant to the identified product category or subcategory.
2.Check Keyword Classification:
•Confirm that keywords are correctly grouped into Primary, Long-tail, and Related categories.
•Ensure each category contains at least 15 keywords.
3.Ensure Compliance:
•Remove any brand names, trademarks, special characters, emojis, prohibited phrases, inappropriate content, promotional language, and shipping details.
4.Validate Synonyms and Variations:
•Ensure the inclusion of synonyms, spelling variations, and different word orders to maximize keyword coverage.
5.Assess Usage Scenarios and Target Users:
•Confirm that related keywords reflect the product’s usage scenarios and target demographics.
6.Verify JSON Formatting:
•Check that the JSON structure is correct, with no syntax errors or trailing commas.
•Ensure keywords within each category are properly separated by commas.
7.Review for Relevance and Specificity:
•Make sure all keywords are specific to the product and avoid generic or unrelated terms.
Example JSON Format:
{
  "Primary Keywords": [
    "Wireless Bluetooth Headphones",
    "Noise Cancelling Headphones",
    "Over-Ear Bluetooth Headphones",
    "Wireless Sports Headphones",
    "Bluetooth Gaming Headset",
    "Wireless Stereo Headphones",
    "Bluetooth Travel Headphones",
    "Wireless Audio Headphones",
    "Bluetooth Workout Headphones",
    "Music Wireless Headphones",
    "Bluetooth Wireless Earphones",
    "Wireless Earbuds",
    "Bluetooth Headphones",
    "Wireless In-Ear Headphones",
    "Bluetooth Sports Earphones"
  ],
  "Long-tail Keywords": [
    "Wireless Bluetooth noise cancelling headphones for sports",
    "Long battery life wireless headphones",
    "Comfortable over-ear Bluetooth headphones for gaming",
    "Wireless Bluetooth headphones with microphone for calls",
    "Lightweight wireless headphones for running",
    "Bluetooth headphones with adjustable headband",
    "Wireless Bluetooth headphones for travel",
    "Noise reducing Bluetooth headphones for office",
    "Bluetooth headphones with deep bass",
    "Children's wireless Bluetooth headphones",
    "Wireless Bluetooth headphones suitable for cycling",
    "Waterproof Bluetooth sports headphones",
    "Wireless Bluetooth headphones with charging case",
    "Noise-cancelling wireless headphones for studying",
    "Wireless Bluetooth headphones suitable for long-term wear"
  ],
  "Related Keywords": [
    "Sports Accessories",
    "Music Equipment",
    "Gaming Accessories",
    "Office Supplies",
    "Travel Gear",
    "Fitness Equipment",
    "Audio Equipment",
    "Tech Gadgets",
    "Personal Electronics",
    "Lifestyle Accessories",
    "Sound Systems",
    "Digital Accessories",
    "Headphone Accessories",
    "Portable Devices",
    "Wireless Devices"
  ]
}
"""


# 把基础表的流量词信息更新到已经打了标签的标签组中
def flush_basic_or_amazon_picture_and_search_words(platform):
    data_ = db_list_by_page("basic_product", "id,search_words", f"platform='{platform}' and product_type !='' and json_text is not null and search_words is not null and published = 1", None, 1, 1000000)
    for row in data_:
        id_ = row[0]
        search_words_map = json.loads(row[1])
        keywords_next = search_words_map.get("Primary Keywords")[0:3] + search_words_map.get("Long-tail Keywords")[0:3] + search_words_map.get("Related Keywords")[0:3]
        generic_keywords = split_langer_txt(keywords_next)
        amazon_us = db_get_one("amazon_us_tag", f'basic_id={id_}')
        if amazon_us is not None:
            print(row[0])
            base_json = json.loads(amazon_us[-2])
            img_map = {"generic_keywords": generic_keywords,
                       "supplier_declared_material_regulation1": "Not Applicable"}
            base_json.update(img_map)
            db_batch_update("amazon_us_tag", ["basic_id", "base_json"], [[id_, json.dumps(base_json)]])


# 手动刷入需要同步的价格和库存 已经上架的数据
def flush_record_via_list(vida_list, shop):
    brand_str = ["-LO-", "-TH-", "-LP-", "-TLH-", "-He-", "-Dy-", "-DY-", "-LB-", "-Ud-", "-XB-", "-TW-", "-SK-", "-SV", "-HM-"]
    brand_zip_list = ["LP", "TH", "LP", "TH", "HM", "Dy", "Dy", "LB", "Ud", "Ud", "Dy", "SV", "SV", "HM"]
    brand_list = ["LOPOO", "TREATLIFE HOME", "LOPOO", "TREATLIFE HOME", "Heemab", "Dyncan", "Dyncan", "LJLB", "Udorich", "Udorich", "Dyncan", "SVRCK", "SVRCK", "Heemab"]
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for sku_data in vida_list:
        record = db_get_one("amazon_us_record", f"sku='{sku_data}'")
        copy_sku = sku_data
        if record:
            continue
        if sku_data.startswith("XL-"):
            sku_data = sku_data.replace("XL-", "")
        else:
            sku_data = sku_data.replace("GG-", "")
        brand = ""
        brand_zip = ""
        basic_sku = ""
        opt_name = ""
        platform = ""
        for brand_tm in brand_str:
            if brand_tm in sku_data:
                basic_sku = sku_data.split(brand_tm)[0]
                br_index = brand_str.index(brand_tm)
                brand = brand_list[br_index]
                brand_zip = brand_zip_list[br_index]
        basic_id = None
        db_prod = db_get_one("basic_product", f"sku ='{basic_sku}'")
        if db_prod:
            basic_id = db_prod[0]
            platform = db_prod[4]
        else:
            print("库源没有,", sku_data)
            continue
        li_ = sku_data.split("-")
        opt_name = li_[-1]
        sku = copy_sku
        remark = "by hand record"
        quantity = 0
        price = ""
        print("成功", sku, basic_sku, platform, opt_name, brand, brand_zip)
        tmp = [1, create_time, basic_id, platform, sku, basic_sku, brand_zip, brand, opt_name, remark, shop, quantity, price, "others", "others"]
        db_batch_insert("amazon_us_record", ["status", "create_time", "basic_id", "basic_platform", "sku",
                                             "basic_sku", "brand_zip", "brand", "opt_name", "remark", "shop", "quantity", "price", "class_name", "product_type"], [tmp])


# 手动刷入需要同步的价格和库存 已经上架的数据
def flush_record_via_list_v2(vida_list, shop):
    brand_str = ["-LO-", "-TH-", "-LP-", "-TLH-", "-He-", "-Dy-", "-DY-", "-LB-", "-Ud-", "-XB-", "-TW-", "-SK-", "-SV", "-HM-"]
    brand_zip_list = ["LP", "TH", "LP", "TH", "HM", "Dy", "Dy", "LB", "Ud", "Ud", "Dy", "SV", "SV", "HM"]
    brand_list = ["LOPOO", "TREATLIFE HOME", "LOPOO", "TREATLIFE HOME", "Heemab", "Dyncan", "Dyncan", "LJLB", "Udorich", "Udorich", "Dyncan", "SVRCK", "SVRCK", "Heemab"]
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    for sku_data in vida_list:
        record = db_get_one("amazon_us_record", f"sku='{sku_data}'",AmazonUsRecord)
        copy_sku = sku_data
        if record:
            continue
        if sku_data.startswith("XL-"):
            sku_data = sku_data.replace("XL-", "")
        else:
            sku_data = sku_data.replace("GG-", "")
        brand = ""
        brand_zip = ""
        basic_sku = ""
        opt_name = ""
        platform = ""
        for brand_tm in brand_str:
            if brand_tm in sku_data:
                basic_sku = sku_data.split("-")[0]
                br_index = brand_str.index(brand_tm)
                brand = brand_list[br_index]
                brand_zip = brand_zip_list[br_index]
        basic_id = None
        db_prod = db_get_one("basic_product", f"sku ='{basic_sku}'",BasicProduct)
        if db_prod:
            basic_id = db_prod.id
            platform = db_prod.platform
        else:
            print("库源没有,", sku_data)
            continue
        li_ = sku_data.split("-")
        opt_name = li_[-1]
        sku = copy_sku
        remark = "by hand record"
        quantity = 0
        price = 0
        tmp = [1, create_time, basic_id, platform, sku, basic_sku, brand_zip, brand, opt_name, remark, shop, quantity, price, db_prod.class_name, db_prod.product_type]
        db_batch_insert("amazon_us_record", ["status", "create_time", "basic_id", "basic_platform", "sku",
                                             "basic_sku", "brand_zip", "brand", "opt_name", "remark", "shop", "quantity", "price", "class_name", "product_type"], [tmp])


if __name__ == '__main__':
    list12 = load_json("C:/Users/hunan/Desktop/excel_sku.json")
    flush_record_via_list_v2(list12, "Forio")
    pass
